﻿SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
GO
SET NUMERIC_ROUNDABORT OFF;


GO
/*
 * Drops all database objects related to the SSAS Partition Manager in order to ensure a clean deploy
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */

USE [AdventureWorksDW2014];

PRINT 'Dropping all objects in the spm schema...';

DECLARE @name nvarchar(200);
DECLARE @schemaId int;
DECLARE @cmd nvarchar(1000);

DECLARE viewCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.views WHERE schema_name([schema_id]) = N'spm';

OPEN viewCursor;
FETCH NEXT FROM viewCursor INTO @name, @schemaId;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP VIEW [' + SCHEMA_NAME(@schemaId) + '].[' + @name + ']'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM viewCursor INTO @name, @schemaId;
END

CLOSE viewCursor;
DEALLOCATE viewCursor;

DECLARE procedureCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.procedures WHERE schema_name([schema_id]) = N'spm';

OPEN procedureCursor;
FETCH NEXT FROM procedureCursor INTO @name, @schemaId;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaId) + '].[' + @name + '];'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM procedureCursor INTO @name, @schemaId;
END

CLOSE procedureCursor;
DEALLOCATE procedureCursor;

-- the tables have to be deleted in a specific order

IF object_id(N'spm.SsasMeasureGroup') is not null 
begin
	print 'Dropping table spm.SsasMeasureGroup';
	DROP TABLE spm.SsasMeasureGroup;
end

IF object_id(N'spm.SsasCube') is not null 
begin
	print 'Dropping table spm.SsasCube';
	DROP TABLE spm.SsasCube;
end

IF object_id(N'spm.SsasDatabase') is not null 
begin
	print 'Dropping table spm.SsasDatabase';
	DROP TABLE spm.SsasDatabase;
end

-- remove any other tables that are part of the SsasPartitionManager
DECLARE tableCursor CURSOR FOR
	SELECT [name], [schema_id] FROM sys.tables WHERE schema_name([schema_id]) = N'spm';

OPEN tableCursor;
FETCH NEXT FROM tableCursor INTO @name, @schemaId;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @cmd = 'DROP TABLE [' + SCHEMA_NAME(@schemaId) + '].[' + @name + ']'
	PRINT @cmd;
	EXEC sp_executesql @cmd;
    FETCH NEXT FROM tableCursor INTO @name, @schemaId;
END
CLOSE tableCursor;
DEALLOCATE tableCursor;

IF EXISTS(select 1 from sys.schemas where name = N'spm')
begin
	print 'Dropping SCHEMA spm';
	DROP SCHEMA [spm];
end
GO


GO
PRINT N'Creating [spm]...';


GO
CREATE SCHEMA [spm]
    AUTHORIZATION [dbo];


GO
PRINT N'Creating [spm].[EventLog]...';


GO
CREATE TABLE [spm].[EventLog] (
    [EventLogId] INT             IDENTITY (-2147483648, 1) NOT NULL,
    [LogTime]    DATETIME        NULL,
    [Thread]     NVARCHAR (10)   NULL,
    [LogLevel]   NVARCHAR (10)   NULL,
    [Logger]     NVARCHAR (255)  NULL,
    [LogMessage] NVARCHAR (4000) NULL,
    PRIMARY KEY CLUSTERED ([EventLogId] ASC)
);


GO
PRINT N'Creating [spm].[SsasCube]...';


GO
CREATE TABLE [spm].[SsasCube] (
    [SsasCubeKey]     INT            IDENTITY (1, 1) NOT NULL,
    [SsasCubeName]    NVARCHAR (100) NOT NULL,
    [SsasDatabaseKey] INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([SsasCubeKey] ASC)
);


GO
PRINT N'Creating [spm].[SsasDatabase]...';


GO
CREATE TABLE [spm].[SsasDatabase] (
    [SsasDatabaseKey]  INT            IDENTITY (1, 1) NOT NULL,
    [SsasDatabaseName] NVARCHAR (100) NOT NULL,
    [SsasModelType]    NVARCHAR (16)  NOT NULL,
    PRIMARY KEY CLUSTERED ([SsasDatabaseKey] ASC)
);


GO
PRINT N'Creating [spm].[SsasServer]...';


GO
CREATE TABLE [spm].[SsasServer] (
    [SsasServerKey]  INT            IDENTITY (1, 1) NOT NULL,
    [SsasServerType] NVARCHAR (10)  NOT NULL,
    [SsasServerName] NVARCHAR (200) NOT NULL,
    PRIMARY KEY CLUSTERED ([SsasServerKey] ASC),
    UNIQUE NONCLUSTERED ([SsasServerType] ASC)
);


GO
PRINT N'Creating [spm].[SsasMeasureGroup]...';


GO
CREATE TABLE [spm].[SsasMeasureGroup] (
    [SsasMeasureGroupKey]   INT           IDENTITY (1, 1) NOT NULL,
    [SsasMeasureGroupName]  NVARCHAR (50) NOT NULL,
    [PartitionPeriod]       NCHAR (1)     NOT NULL,
    [PartitionStartDate]    DATE          NOT NULL,
    [PartitionSliceIsRange] BIT           NOT NULL,
    [DateColumnName]        NVARCHAR (30) NOT NULL,
    [DateColumnDataType]    NVARCHAR (20) NOT NULL,
    [SsasCubeKey]           INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([SsasMeasureGroupKey] ASC)
);


GO
PRINT N'Creating unnamed constraint on [spm].[SsasMeasureGroup]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD DEFAULT ('M') FOR [PartitionPeriod];


GO
PRINT N'Creating unnamed constraint on [spm].[SsasMeasureGroup]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD DEFAULT ('20110101') FOR [PartitionStartDate];


GO
PRINT N'Creating unnamed constraint on [spm].[SsasMeasureGroup]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD DEFAULT (0) FOR [PartitionSliceIsRange];


GO
PRINT N'Creating unnamed constraint on [spm].[SsasMeasureGroup]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD DEFAULT ('int') FOR [DateColumnDataType];


GO
PRINT N'Creating [spm].[FK_SsasCube_SsasDatabase]...';


GO
ALTER TABLE [spm].[SsasCube]
    ADD CONSTRAINT [FK_SsasCube_SsasDatabase] FOREIGN KEY ([SsasDatabaseKey]) REFERENCES [spm].[SsasDatabase] ([SsasDatabaseKey]);


GO
PRINT N'Creating [spm].[FK_MeasureGroup_OlapCube]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD CONSTRAINT [FK_MeasureGroup_OlapCube] FOREIGN KEY ([SsasCubeKey]) REFERENCES [spm].[SsasCube] ([SsasCubeKey]);


GO
PRINT N'Creating [spm].[CK_SsasDatabase_ModelType]...';


GO
ALTER TABLE [spm].[SsasDatabase]
    ADD CONSTRAINT [CK_SsasDatabase_ModelType] CHECK (SsasModelType in (N'Multidimensional',N'Tabular'));


GO
PRINT N'Creating [spm].[CK_SsasMeasureGroup_DateColumnDataType]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD CONSTRAINT [CK_SsasMeasureGroup_DateColumnDataType] CHECK (DateColumnDataType in (N'int',N'date',N'datetime'));


GO
PRINT N'Creating [spm].[CK_SsasMeasureGroup_DateColumnName]...';


GO
ALTER TABLE [spm].[SsasMeasureGroup]
    ADD CONSTRAINT [CK_SsasMeasureGroup_DateColumnName] CHECK (len(DateColumnName)>0);


GO
PRINT N'Creating [spm].[SsasPartitionDefinitions]...';


GO
CREATE VIEW spm.SsasPartitionDefinitions
AS 
/*
 * View to support the SSAS Partition Manager.  See https://SsasPartitionManager.codeplex.com/documentation for full documentation.
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
SELECT 
	C.SsasDatabaseName,
	C.SsasModelType,
	B.SsasCubeName,
	A.SsasMeasureGroupName,
	A.PartitionPeriod,
	A.PartitionStartDate,
	A.DateColumnName,
	A.DateColumnDataType,
	A.PartitionSliceIsRange
FROM spm.SsasMeasureGroup A
	JOIN spm.SsasCube B ON
		A.SsasCubeKey = B.SsasCubeKey
	JOIN spm.SsasDatabase C ON
		B.SsasDatabaseKey = C.SsasDatabaseKey;
GO
PRINT N'Creating [spm].[SsasDateDimWrapper]...';


GO
CREATE VIEW spm.SsasDateDimWrapper
AS 
/*
 * View to support the SSAS Partition Manager.  See https://SsasPartitionManager.codeplex.com/documentation for full documentation.
 * See comments below.
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
SELECT
	/* 
	 * Change this to get the same results from your own date dimension table
	 */
	D.DateKey as DateKey,
	cast(FullDateAlternateKey as date) as ActualDate,

	-- all the following should be strings
	N'[Date].[Date]' as DailyPartitionSliceMember,
	N'&[' + cast(D.DateKey as nvarchar(8)) + N']' as DailyPartitionSliceKey,
	cast(CalendarYear as nvarchar(4)) as CalendarYear,
	cast(CalendarQuarter as nvarchar(1)) as CalendarQuarter, 
	right(N'0' + cast(month(FullDateAlternateKey) as nvarchar(2)),2) as CalendarMonth, 
	right(N'0' + cast(WeekNumberOfYear as nvarchar(2)),2) as WeekNumberOfYear,
	right(N'00' + cast(DayNumberOfYear as nvarchar(3)),3) as DayNumberOfYear
FROM dbo.DimDate D  -- change this to being your local date dimension
GO
PRINT N'Creating [spm].[SsasPartitionPeriods]...';


GO
CREATE VIEW spm.SsasPartitionPeriods
AS
/*
 * Copyright (c) 2015 Dr. John Tunnicliffe, Decision Analytics
 * You are free to use this software under the terms of the licence: https://SsasPartitionManager.codeplex.com/license
 *
 * SsasPartitionPeriods view to support the SSAS Partition Manager.  
 * See https://SsasPartitionManager.codeplex.com/documentation for full documentation.
 *
 * This is the view you need to tailor to match your cube definition and specific partitioning requirements. 
 * It contains hard-coded MDX. In this case, the hard-coded MDX is specific to the date dimension of the Adventure Works cube.
 *
 * The following are examples of the MDX unique names required by the Date dimension in the Adventure Works cube
 * 
 * Key attribute:
 *		[Date].[Date].&[20050116]
 *
 * Levels in the [Date].[Calendar] hierarchy
 *		[Date].[Calendar].[Calendar Year].&[2005]
 *		[Date].[Calendar].[Calendar Semester].&[2008]&[1]
 *		[Date].[Calendar].[Calendar Quarter].&[2008]&[2]
 *		[Date].[Calendar].[Month].&[2005]&[10]
 *		[Date].[Calendar].[Date].&[20050108]
 *
 * Weekly calendar
 *		[Date].[Calendar Weeks].[Calendar Week].&[1]&[2007]
 *
 * Part of the SsasPartitionManager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
 WITH PartitionSchemes AS (
	-- Yearly partition periods
	SELECT  
		N'Y' as PartitionPeriod,
		CalendarYear as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Calendar Year]' as PartitionSliceMember, 
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube e.g. &[2005]
		-- e.g. [Date].[Calendar].[Calendar Year].&[2005]
		N'&[' + CalendarYear + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Quarterly partition periods
	SELECT  
		N'Q' as PartitionPeriod,
		CalendarYear + N'Q' + CalendarQuarter as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Calendar Quarter]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube e.g. &[2008]&[2]
		-- e.g. [Date].[Calendar].[Calendar Quarter].&[2008]&[2]
		N'&[' + CalendarYear + N']&[' + CalendarQuarter + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, CalendarQuarter) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, CalendarQuarter) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Monthly partition periods
	SELECT  
		N'M' as PartitionPeriod,
		CalendarYear + N'M' + CalendarMonth as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar].[Month]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube
		-- e.g. [Date].[Calendar].[Month].&[2005]&[10]
		N'&[' + CalendarYear + N']&[' + CalendarMonth + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, CalendarMonth) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, CalendarMonth) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 

	-- Weekly partition periods
	SELECT 
		N'W' as PartitionPeriod,
		CalendarYear + N'W' + WeekNumberOfYear as PartitionSuffix,
		-- You need to adjust the PartitionSliceMember to return the correct MDX for your Multidimensional cube - if tabular you can set to NULL
		N'[Date].[Calendar Weeks].[Calendar Week]' as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		-- Below we use the key collection used by the AdventureWorks cube
		-- e.g. [Date].[Calendar Weeks].[Calendar Week].&[1]&[2007]
		N'&[' + WeekNumberOfYear + N']&[' + CalendarYear + N']' as PartitionSliceKey,
		Min(ActualDate) OVER (PARTITION BY CalendarYear, WeekNumberOfYear) as PeriodStartDate,
		Max(ActualDate) OVER (PARTITION BY CalendarYear, WeekNumberOfYear) as PeriodEndDate
	FROM spm.SsasDateDimWrapper D

	UNION 
	
	-- Daily partition periods
	SELECT 
		N'D' as PartitionPeriod,
		CalendarYear + N'D' + DayNumberOfYear as PartitionSuffix,
		DailyPartitionSliceMember as PartitionSliceMember,
		-- You need to adjust the PartitionSliceKey to return the correct format for your cube
		N'&[' + cast(D.DateKey as nvarchar(8)) + N']' as PartitionSliceKey,
		D.ActualDate as PeriodStartDate,
		D.ActualDate as PeriodEndDate
	FROM spm.SsasDateDimWrapper D
)
SELECT
	PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
UNION
-- Add a special hybrid partition scheme which is Monthly partition periods for 2014 and quarterly partitions for previous years
-- Note that AdventureWorks's date dimension stops in 2014, so we hard code this year for demo purposes
-- Clearly in a prod environment, this should be SARG and using a proper date dimension which has 2015 in it!
SELECT  
	N'H' as PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
WHERE PartitionPeriod = N'M' and year(PeriodStartDate) >= 2014
UNION
SELECT  
	N'H' as PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	PartitionSuffix,
	PartitionSliceMember,
	PartitionSliceKey
FROM PartitionSchemes
WHERE PartitionPeriod = N'Q' and year(PeriodStartDate) < 2014
GO
PRINT N'Creating [spm].[SsasPartitions]...';


GO
CREATE VIEW spm.SsasPartitions
AS 
/*
 * Copyright (c) 2015 Dr. John Tunnicliffe, Decision Analytics
 * You are free to use this software under the terms of the following licence: https://SSASPartitionManager.codeplex.com/license
 *
 * View to support the SSAS Partition Manager.  See https://SSASPartitionManager.codeplex.com/documentation for documentation.
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SSASPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
WITH SsasPartitions AS (
	SELECT 
		A.SsasDatabaseName,
		A.SsasModelType,
		A.SsasCubeName,
		A.SsasMeasureGroupName,	
		P.PartitionSuffix,
		A.PartitionPeriod,
		A.DateColumnName,
		A.DateColumnDataType,
		P.PeriodStartDate,
		P.PeriodEndDate,
		year(P.PeriodStartDate) * 10000 + month(P.PeriodStartDate) * 100 + day(P.PeriodStartDate) as PeriodStartDateKey,
		year(P.PeriodEndDate) * 10000 + month(P.PeriodEndDate) * 100 + day(P.PeriodEndDate) as PeriodEndDateKey,
		P.PartitionSliceMember,
		A.PartitionSliceIsRange,
		P.PartitionSliceKey
	FROM spm.SsasPartitionDefinitions as A
		CROSS JOIN spm.SsasPartitionPeriods as P
	WHERE 
		A.PartitionPeriod = P.PartitionPeriod
		AND PeriodStartDate BETWEEN PartitionStartDate AND GETUTCDATE()
),
WithDateRange AS
(
	-- obtain the full range of dates between the PeriodStartDateKey and PeriodEndDateKey so we can generate a MDX range if required for our Multidimensional cubes
	SELECT 
		DailyPartitionSliceKey,
		DailyPartitionSliceMember,
		SsasDatabaseName,
		SsasCubeName,
		SsasMeasureGroupName,
		PeriodStartDateKey,
		PeriodEndDateKey,
		D.DateKey
	from SsasPartitions as A		
		join spm.SsasDateDimWrapper as D on  
			D.DateKey between A.PeriodStartDateKey and A.PeriodEndDateKey
)
SELECT 
	SsasDatabaseName,
	SsasModelType,
	SsasCubeName,
	SsasMeasureGroupName,
	SsasMeasureGroupName + N' ' + PartitionSuffix as PartitionName,
	PartitionPeriod,
	PeriodStartDate,
	PeriodEndDate,
	case
		when SsasModelType = N'Multidimensional' then 
			case
			-- period partition slice
			when PartitionSliceMember is not null and PartitionSliceIsRange = 0 
				then N'{' + PartitionSliceMember + N'.' + PartitionSliceKey + N'}'

			-- range partition slice
			when PartitionSliceMember is not null and PartitionSliceIsRange = 1 
				then replace(N'{' + 
					-- we use stuff to remove the first comma in the range
					stuff(
					(
						select 
							N',' + DailyPartitionSliceMember + N'.' + DailyPartitionSliceKey
						from WithDateRange as B		-- contains the full range of dates between the PeriodStartDateKey and PeriodEndDateKey so we can generate a MDX range if required
						where A.SsasDatabaseName = B.SsasDatabaseName
							and A.SsasCubeName = B.SsasCubeName
							and A.SsasMeasureGroupName = B.SsasMeasureGroupName
							and A.PeriodStartDateKey = B.PeriodStartDateKey
							and A.PeriodEndDateKey = B.PeriodEndDateKey
						order by B.DateKey
						for xml path('')
					), 1, 1, '') + N'}',
				N'&amp;',N'&')
			end 
		else null
	end as PartitionSlice,
	case lower(DateColumnDataType)
		when N'int' then
           -- for integer fields use between 
           DateColumnName + N' between ' + cast(PeriodStartDateKey as nvarchar(8)) + N' and ' + cast(PeriodEndDateKey as nvarchar(8))

	    -- Note that SQL Server knows how to interpret strings in ISO8601 format such as yyyyMMdd, 
	    -- so the code below simply uses the key we generated above
		when N'date' then
           -- for date fields use between as there is no time element
		   DateColumnName + N' between ''' + cast(PeriodStartDateKey as nvarchar(8)) + N''' and ''' + cast(PeriodEndDateKey as nvarchar(8)) + N''''
		when N'datetime' then
			-- for datetime fields, use >= and < date + 1 day as the time element could screw things up
		    DateColumnName + N' >= ''' + cast(PeriodStartDateKey as nvarchar(8)) + N''' and ' + DateColumnName + N' < dateadd(dd,1,''' + cast(PeriodEndDateKey as nvarchar(8)) + N''')'
	end as PartitionWhereClause
FROM SsasPartitions as A;
GO
PRINT N'Creating [spm].[LogEvent]...';


GO
CREATE PROCEDURE spm.LogEvent
	@logTime datetime,
	@thread nvarchar(10),
	@logLevel nvarchar(10),
	@logger nvarchar(255),
	@logMessage nvarchar(4000)
AS
BEGIN
	/*
	 * Stored proc to write logging information from the SSAS Partition Manager.
	 * This stored proc is only called when the ADONetAppender_SqlServer appender is enabled in the .config file
     *
	 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
	 * 
	 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
	 * Available for consultancy assignments and speaking engagements
     * 	
	 * eMail: john@decision-analytics.co.uk 
	 * http://www.decision-analytics.co.uk/
	 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
	 * https://www.linkedin.com/in/drjohntunnicliffe
	 */

	INSERT INTO spm.EventLog (LogTime, Thread, LogLevel, Logger, LogMessage) VALUES (@logTime, @thread, @logLevel, @logger, @logMessage);
END
GO
/*
 * Post-Deployment Script to populate the partition definition tables for use by the SSAS Partition Manager
 *
 * This should be modified to meet your requirements.  This example is targeted against the Adventure Works cube.
 *
 * See https://SsasPartitionManager.codeplex.com/documentation for full documentation. 
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */

declare @OlapDatabaseName nvarchar(100) = N'AdventureWorksDW2014Multidimensional-EE';
declare @OlapCubeName nvarchar(100) = N'Adventure Works';
declare @TabularDatabaseName nvarchar(100) = N'AW Tabular Model SQL 2014';
declare @TabularCubeName nvarchar(100) = N'Model';



/*
 * Populate the SsasDatabase table using a merge
 */
MERGE spm.SsasDatabase AS T
USING (
	VALUES 
	(@OlapDatabaseName, N'Multidimensional'),
	(@TabularDatabaseName, N'Tabular')
	) AS S(SsasDatabaseName, SsasModelType)
ON
	T.SsasDatabaseName = S.SsasDatabaseName
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasDatabaseName, SsasModelType)
			VALUES	(SsasDatabaseName, SsasModelType);

/*
 * Populate the SsasCube table using a merge
 */
MERGE spm.SsasCube AS T
USING (
	SELECT SsasDatabaseKey, @OlapCubeName as SsasCubeName from spm.SsasDatabase where SsasDatabaseName = @OlapDatabaseName
	union
	SELECT SsasDatabaseKey, @TabularCubeName as SsasCubeName from spm.SsasDatabase where SsasDatabaseName = @TabularDatabaseName
) AS S (SsasDatabaseKey, SsasCubeName)
ON
	T.SsasCubeName = S.SsasCubeName
	and T.SsasDatabaseKey = S.SsasDatabaseKey
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasDatabaseKey, SsasCubeName)
			VALUES	(SsasDatabaseKey, SsasCubeName);

/*
 * Populate the SsasMeasureGroup table using a merge
 */
MERGE spm.SsasMeasureGroup AS T
USING (

	-- Yearly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Customers' as SsasMeasureGroupName, 
		N'Y' AS PartitionPeriod,  
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	-- Quarterly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Sales' as SsasMeasureGroupName, 
		N'Q' AS PartitionPeriod,   
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	-- Monthly partitioning with MDX range in PartitionSlice (i.e. PartitionSliceIsRange = 1) for Multidimensional
	SELECT SsasCubeKey, 
		N'Internet Orders' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,  
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,		
		N'20110101' as PartitionStartDate,
		1 as PartitionSliceIsRange  -- we want a range of dates covering the full month e.g {[Date].[Date].&[20130101],[Date].[Date].&[20130102],[Date].[Date].&[20130103],[Date].[Date].&[20130104], etc. 
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Monthly partitioning with MDX member in PartitionSlice (i.e. PartitionSliceIsRange = 0) for Multidimensional
	SELECT SsasCubeKey, 
		N'Reseller Orders' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Weekly partitioning for Multidimensional
	SELECT SsasCubeKey, 
		N'Reseller Sales' as SsasMeasureGroupName, 
		N'W' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Hybrid partitioning where 2014+ is monthly and before 2014 partitioning is quarterly for Multidimensional
	SELECT SsasCubeKey, 
		N'Sales Orders' as SsasMeasureGroupName, 
		N'H' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20110101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @OlapCubeName and SsasDatabaseName = @OlapDatabaseName

UNION ALL

	--  Yearly partitioning for Tabular
	SELECT SsasCubeKey, 
		N'Reseller Sales' as SsasMeasureGroupName, 
		N'Y' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20090101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @TabularCubeName and SsasDatabaseName = @TabularDatabaseName

UNION ALL

	--  Monthly partitioning for Tabular
	SELECT SsasCubeKey, 
		N'Internet Sales' as SsasMeasureGroupName, 
		N'M' AS PartitionPeriod,
		N'OrderDateKey' AS DateColumnName,
		N'int' AS DateColumnDataType,
		N'20090101' as PartitionStartDate,
		0 as PartitionSliceIsRange
	from spm.SsasCube A
	join spm.SsasDatabase B on 
		A.SsasDatabaseKey = B.SsasDatabaseKey
	where SsasCubeName = @TabularCubeName and SsasDatabaseName = @TabularDatabaseName
) AS S (SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange)
ON
	T.SsasMeasureGroupName = S.SsasMeasureGroupName
	and T.SsasCubeKey = S.SsasCubeKey
WHEN MATCHED 
THEN	UPDATE 
		SET SsasCubeKey = S.SsasCubeKey, 
		SsasMeasureGroupName = S.SsasMeasureGroupName, 
		PartitionPeriod = S.PartitionPeriod, 
		DateColumnName = S.DateColumnName, 
		DateColumnDataType = S.DateColumnDataType, 
		PartitionStartDate = S.PartitionStartDate,
		PartitionSliceIsRange = S.PartitionSliceIsRange
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange)
			VALUES	(SsasCubeKey, SsasMeasureGroupName, PartitionPeriod, DateColumnName, DateColumnDataType, PartitionStartDate, PartitionSliceIsRange);

GO

-- Populate the server list
MERGE spm.SsasServer AS T
USING (
	VALUES 
	(N'UAT', N'yourUATServer'),
	(N'PROD', N'yourPRODServer'),
	(N'DEV', N'localhost'),
	(N'DEV_TAB', N'localhost\ast')
	) AS S (SsasServerType, SsasServerName)
ON
	T.SsasServerName = S.SsasServerName
WHEN NOT MATCHED BY TARGET
THEN		INSERT	(SsasServerType, SsasServerName)
			VALUES	(SsasServerType, SsasServerName);
GO


GO

GO
PRINT N'Update complete.';


GO
